package com.pla.googlecalendarviews;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

public class ViewDAO {

  public static void main(String[] args) throws Exception {
    ViewDAO viewDAO = new ViewDAO();
    //   viewDAO.createTable();
    ArrayList<View> views = viewDAO.get(2);
    for (View view : views) {
      System.out.println(view.getAccountId() + " " + view.getName() + " " + view.getViewId() + " " + view.getCalendars().size());
    }
  }

  public ArrayList<View> get(int accountId) {
    ArrayList<View> arrayList = new ArrayList<View>();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("Select * from views where accountId = ?");
      statement.setInt(1, accountId);
      rs = statement.executeQuery();
      while (rs.next()) {
        View view = new View();
        view.setViewId(rs.getInt("viewid"));
        view.setName(rs.getString("name"));
        view.setCalendars(getViewCalendars(view.getViewId()));
        view.setAccountId(accountId);
        view.setExists(true);
        arrayList.add(view);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return arrayList;
  }

  public int delete(View view) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("delete from views where viewid = ?");
      statement.setInt(1, view.getViewId());
      return statement.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(statement, connection);
    }
    return 0;
  }

  public int rename(View view) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("update views set name = ?  where viewid = ?");
      statement.setString(1, view.getName());
      statement.setInt(2, view.getViewId());
      return statement.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(statement, connection);
    }
    return 0;
  }
 public int updateView(View view) {
    int recordsUpdated = 0;
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      if (view.isExists()) {
        statement = connection.prepareStatement("update views set name = ? where viewid = ?");
        statement.setString(1, view.getName());
        statement.setInt(2, view.getViewId());
        int x = statement.executeUpdate();
        System.out.println(x + " view records updated");
        Utils.close(statement);
      } else {
        statement = connection.prepareStatement("insert into views (accountid, name) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
        statement.setInt(1, view.getAccountId());
        statement.setString(2, view.getName());
        int x = statement.executeUpdate();
        System.out.println(x + " view records added");
        rs = statement.getGeneratedKeys();
        if (rs.next()) {
          view.setViewId(rs.getInt(1));
          view.setExists(true);
        }
        Utils.close(statement);
      }
      statement = connection.prepareStatement("delete from viewsdetail where viewid = ?");
      statement.setInt(1, view.getViewId());
      statement.executeUpdate();
      Utils.close(statement);
      statement = connection.prepareStatement("insert into viewsdetail (viewid, calendarid, title, hidden, selected, rgb) values(?,?,?,?,?,?)");
      ArrayList<Calendar> calendars = view.getCalendars();
      for (Calendar calendar : calendars) {
        int i = 1;
        statement.setInt(i++, view.getViewId());
        statement.setString(i++, calendar.getCalendarId());
        statement.setString(i++, calendar.getTitle());
        statement.setString(i++, Utils.isYes(calendar.isHidden()));
        statement.setString(i++, Utils.isYes(calendar.isSelected()));
        if (Utils.isBlank(calendar.getRgb())) {
          calendar.setRgb("#CC3333");
        }
        statement.setString(i++, calendar.getRgb().toUpperCase());
        recordsUpdated += statement.executeUpdate();
      }
      System.out.println(recordsUpdated + " calendar records inserted");
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return recordsUpdated;
  }

  private ArrayList<Calendar> getViewCalendars(int viewId) {
    ArrayList<Calendar> arrayList = new ArrayList<Calendar>();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("Select a.* from viewsdetail as a where a.viewid = ?");
      statement.setInt(1, viewId);
      rs = statement.executeQuery();
      while (rs.next()) {
        Calendar calendar = new Calendar();
        calendar.setHidden(Utils.isYes(rs.getString("hidden")));
        calendar.setRgb(rs.getString("rgb"));
        calendar.setSelected(Utils.isYes(rs.getString("selected")));
        calendar.setTitle(rs.getString("title"));
        calendar.setCalendarId(rs.getString("calendarId"));
        calendar.setExists(true);
        arrayList.add(calendar);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    Collections.sort(arrayList);
    return arrayList;
  }

  public void createTable() throws Exception {
    Connection connection = null;
    Statement statement = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.createStatement();
      try {
        statement.execute("drop table views");
        statement.execute("drop table viewsdetail");
      } catch (SQLException e) {
        e.printStackTrace();
      }
      statement.execute("create table views (viewid int generated always as identity, accountid int, name varchar(132), primary key(viewid, accountid))");
      statement.execute("create table viewsdetail (viewdetailid int generated always as identity, viewid int, calendarid varchar(256), title varchar(256), hidden char(1), selected char(1), rgb char(7), primary key(viewid, calendarid))");
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(statement, connection);
    }
  }
}
